﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace KTX.Data
{
    public abstract class BaseDAL
    {
        protected SqlConnection Cnn { get; set; }
        protected SqlCommand Cmd { get; set; }       
        
        string ketnoi = ConfigurationManager.ConnectionStrings["project"].ConnectionString;        
        /// <summary>
        /// Constructor
        /// </summary>

        public BaseDAL()
        {
            Cnn = new SqlConnection(ketnoi); // default connection string
            Cmd = new SqlCommand() { Connection = Cnn };
        }

        public BaseDAL(string connectString)
        {
            Cnn = new SqlConnection(connectString);
            Cmd = new SqlCommand() { Connection = Cnn };
        }

        /// <summary>
        /// Open connection
        /// </summary>
        /// <returns>True is open successfull else false</returns>
        protected bool Open()
        {
            try
            {
                if (Cnn.State == ConnectionState.Closed)
                    Cnn.Open();
                return true;
            }
            catch { return false; }
        }

        /// <summary>
        /// Close connection
        /// </summary>
        protected void Close()
        {
            if (Cnn.State != ConnectionState.Closed)
                Cnn.Close();
        }

        /// <summary>
        /// Execute query SQL command text
        /// </summary>
        /// <param name="sqlCommand">T-SQL</param>
        /// <param name="tableName">table name</param>
        /// <returns>data</returns>
        protected DataTable ExecuteQuery(string sqlCommand, string tableName = "Tmp")
        {
            try
            {
                Open();
                Cmd.CommandText = sqlCommand;
                var tbl = new DataTable(tableName);
                tbl.Load(Cmd.ExecuteReader());
                return tbl;
            }
            catch { return null; }
            finally { Close(); }
        }

        /// <summary>
        /// Execute non query SQL command text
        /// </summary>
        /// <param name="sqlCommand">T-SQL</param>
        /// <returns>number of records affect</returns>
        protected int ExecuteNonQuery(string sqlCommand)
        {
            try
            {
                Open();
                Cmd.CommandText = sqlCommand;
                return Cmd.ExecuteNonQuery();
            }
            catch { return -1; }
            finally { Close(); }
        }
    }
}
